﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_Business;

namespace Business.Admin
{
    /// <summary>
    /// 车辆管理系统
    /// </summary>
    public class CarManage
    {
        #region 车辆
        /// <summary>
        /// 返回车辆基本信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetCarBasic(string whereValue)
        {
            string strSql = @"select t.*,(select user_name from st_user a where a.userid=t.jsyxm) username  from XT_CLGL_BASIC t where 1 = 1";
            if (!string.IsNullOrEmpty(whereValue))
            {
                strSql += whereValue;
            }
            DataTable dt = null;

            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }
        #endregion

        #region  删除车辆
        /// <summary>
        /// 删除车辆信息
        /// </summary>
        /// <param name="id"></param>
        public static void DelCarInfo(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                string strSql = string.Format("delete from XT_CLGL_BASIC where id ='{0}'", id);
                SysParams.OAConnection().RunSql(strSql);
            }
        }
        #endregion

        /// <summary>
        /// 获取驾驶员信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetJsy()
        {
            string strSql = @"select a.userid, a.user_name
  from st_user a, st_user_department b, st_department c
 where a.userid = b.userid
   and b.order_id = c.departid
   and c.depart_name like '%驾驶%'";
            DataTable dtSource = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }

        /// <summary>
        /// 获取车辆运行统计表
        /// </summary>
        /// <param name="strDateStart">开始时间</param>
        /// <param name="strDateEnd">终止时间</param>
        /// <returns></returns>
        public static DataTable GetRunCollectInfo(string strDateStart, string strDateEnd)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(strDateStart))
            {
                strWhere = string.Format(" and k.finish_time >= to_date('{0}', 'yyyy-mm-dd')", strDateStart); 
            }
            if (!string.IsNullOrEmpty(strDateEnd))
            {
                strWhere += string.Format(" and k.finish_time <= to_date('{0}', 'yyyy-mm-dd')", strDateEnd);
            }

            string strSql = string.Format(@"select cph,
       (select user_name from st_user b where b.userid = a.jsyxm) username,
       a.yhbz,
        (select sum(nvl(短途里程, 0) + nvl(长途里程, 0))
          from UT_用车申请单 t, st_instance k
         where 派遣车号 = a.cph and t.iid = k.iid {0}) zgls,
       (select 实际油耗
          from UT_用车申请单 t, st_instance k
         where 派遣车号 = a.cph and t.iid = k.iid {0}) sjyh,
       (select sum(nvl(长途里程, 0))
          from UT_用车申请单 t, st_instance k
         where 派遣车号 = a.cph
           and t.iid = k.iid {0}) ctgls,
       (select count(长途里程)
          from UT_用车申请单 t, st_instance k
         where 派遣车号 = a.cph
           and 长途里程 is not null
           and t.iid = k.iid  {0}) ctcs,
       (select sum(nvl(耗油费用, 0))
          from UT_用车申请单 t, st_instance k
         where 派遣车号 = a.cph and t.iid = k.iid {0}) hyje,
       (select (select sum(nvl(实际金额, 0))
                  from UT_车辆保养审批单_CHILD m
                 where m.iid = n.iid)
          from UT_车辆保养审批单 n, st_instance k
         where n.车牌号 = cph
           and n.iid = k.iid  {0} ) wxje,
       (select (select wm_concat(维修项目)
                  from UT_车辆保养审批单_CHILD m
                 where m.iid = n.iid)
          from UT_车辆保养审批单 n, st_instance k
         where n.车牌号 = cph
           and n.iid = k.iid  {0}) wxxm
  from xt_clgl_basic a", strWhere);
            DataTable dtSource = new DataTable();
            SysParams.OAConnection().RunSql(strSql, out dtSource);
            return dtSource;
        }
    }
}
